package com.crm.daoImpl;

import com.crm.dao.VisiterDao;
import com.crm.pojo.Visiter;
import com.crm.pojo.VisiterVo;
import com.crm.util.jdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @version : 1.0
 * @auther : hjx
 * @Date : 2021/6/29
 * @Description : 实训
 */
public class VisiterDaoImpl implements VisiterDao {

    @Override
    public Boolean insertVisiter(Visiter visiter) {

        Connection conn = null;
        String sql = "insert into sale_visit(visit_cust_id,visit_user_id,visit_time,visit_interviewee,visit_addr,visit_detail,visit_next_time) VALUES(?,?,?,?,?,?,?)";

        PreparedStatement ps = null;

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,visiter.getVisitCustId());
            ps.setInt(2,visiter.getVisitUserId());
            ps.setDate(3, new Date(visiter.getVisitTime().getTime()));
            ps.setString(4,visiter.getVisitInterviewee());
            ps.setString(5,visiter.getVisitAddr());
            ps.setString(6,visiter.getVisitDetail());
            ps.setDate(7, new Date(visiter.getVisitNextTime().getTime()));
            int i = ps.executeUpdate();

            if (i == 1) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,null);
        }
        return false;
    }

    @Override
    public List<VisiterVo> selectAllVisitVo() {
        Connection conn = null;
        String sql = "SELECT v.visit_id,c.cust_name," +
                "u.user_name,v.visit_time,v.visit_interviewee,v.visit_addr," +
                "v.visit_detail," +
                "v.visit_next_time " +
                "from sale_visit v,cst_customer c,sys_user u " +
                "WHERE v.visit_cust_id = c.cust_id AND v.visit_user_id = u.user_id";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<VisiterVo> list = new ArrayList<>();
        
        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int visitId = rs.getInt(1);
                String custName = rs.getString(2);
                String name = rs.getString(3);
                Date visitTime = rs.getDate(4);
                String visitInterviewee = rs.getString(5);
                String visitAddr = rs.getString(6);
                String visitDetail = rs.getString(7);
                Date visitNextTime = rs.getDate(8);
                list.add(new VisiterVo(visitId,custName,name,visitTime,visitInterviewee,visitAddr,visitDetail,visitNextTime));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }

        return list;
    }

    @Override
    public List<VisiterVo> selectVisitByName(String visitName) {
        Connection conn = null;
        String sql = "SELECT v.visit_id, c.cust_name, u.user_name," +
                " v.visit_time, v.visit_interviewee, v.visit_addr, " +
                "v.visit_detail, v.visit_next_time FROM sale_visit v," +
                " cst_customer c, sys_user u WHERE v.visit_cust_id = c.cust_id AND" +
                " v.visit_user_id = u.user_id AND v.visit_interviewee LIKE ? ";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<VisiterVo> list = new ArrayList<>();

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setString(1,'%' + visitName + '%');
            rs = ps.executeQuery();
            while (rs.next()) {
                int visitId = rs.getInt(1);
                String custName = rs.getString(2);
                String name = rs.getString(3);
                Date visitTime = rs.getDate(4);
                String visitInterviewee = rs.getString(5);
                String visitAddr = rs.getString(6);
                String visitDetail = rs.getString(7);
                Date visitNextTime = rs.getDate(8);
                list.add(new VisiterVo(visitId,custName,name,visitTime,visitInterviewee,visitAddr,visitDetail,visitNextTime));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public int deleteVisit(int visitId) {
        Connection conn = null;
        String sql = "DELETE from sale_visit WHERE visit_id = ?";
        PreparedStatement ps = null;
        int i = 0;

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,visitId);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,null);
        }
        return i;
    }

    @Override
    public Visiter queryVisitById(int visitId) {
        Connection conn = null;
        String sql = "select * from  sale_visit where visit_id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Visiter visiter = null;

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,visitId);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String custName = rs.getString(2);
                String name = rs.getString(3);
                Date visitTime = rs.getDate(4);
                String visitInterviewee = rs.getString(5);
                String visitAddr = rs.getString(6);
                String visitDetail = rs.getString(7);
                Date visitNextTime = rs.getDate(8);
                visiter = new Visiter(id,0,0,visitTime,visitInterviewee,visitAddr,visitDetail,visitNextTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }
        return visiter;
    }

    @Override
    public int updateVisit(Visiter visiter) {
        Connection conn = null;
        String sql = "UPDATE sale_visit SET visit_cust_id=?,visit_user_id=?," +
                "visit_time = ?,visit_interviewee=?,visit_addr=?," +
                "visit_detail = ?,visit_next_time= ? WHERE visit_id = ?";
        PreparedStatement ps = null;
        int i = 0;

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,visiter.getVisitCustId());
            ps.setInt(2,visiter.getVisitUserId());
            ps.setDate(3, new Date(visiter.getVisitTime().getTime()));
            ps.setString(4,visiter.getVisitInterviewee());
            ps.setString(5,visiter.getVisitAddr());
            ps.setString(6,visiter.getVisitDetail());
            ps.setDate(7, new Date(visiter.getVisitNextTime().getTime()));
            ps.setInt(8,visiter.getVisitId());
            i = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,null);
        }
        return i;
    }
}
